package javaseof10day.day7.am;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;



public class UsersAction {

	public int add(String name, String password, String email) throws ClassNotFoundException, SQLException {

		// INSERT INTO table_name ( field1, field2,...fieldN )VALUES ( value1,
		// value2,...valueN );
		Statement stmt = DBuitls.getconn();
		String sql = "INSERT INTO users (name,password,email) VALUES ('" + name + "','" + password + "','" + email
				+ "')";
		int res = stmt.executeUpdate(sql);
		System.out.println(res);
		// 完成后关闭

		DBuitls.connclose();

		return res;
	}

	public int update(Users users) throws SQLException, ClassNotFoundException {

		Users resUsers = selectOne(users.getId());

		if (resUsers != null) {
			// INSERT INTO table_name ( field1, field2,...fieldN )VALUES ( value1,
			// value2,...valueN );
			Statement stmt = DBuitls.getconn();
			String sql = "update users set name='" + users.getName() + "', password='" + users.getPassword()
					+ "', email='" + users.getEmail() + "' where id=" + users.getId();
			int res = stmt.executeUpdate(sql);
			System.out.println(res);
			// 完成后关闭

			DBuitls.connclose();
			return res;

		}
		return 0;

	}

	public int deleteOne(int id) throws SQLException, ClassNotFoundException {

		Users resUsers = selectOne(id);

		if (resUsers != null) {

			Statement stmt = DBuitls.getconn();

			String sql = "DELETE FROM  users WHERE  id=" + id;
			int rs = stmt.executeUpdate(sql);

			// 完成后关闭

			DBuitls.connclose();
			return rs;
		}
		return 0;

	}

	public Users selectOne(int id) throws SQLException, ClassNotFoundException {

		Statement stmt = DBuitls.getconn();

		String sql = "SELECT *  FROM users WHERE id=" + id;
		ResultSet rs = stmt.executeQuery(sql);

		rs.next();

		Users users = new Users(rs.getInt("id"), rs.getString("name"), rs.getString("password"), rs.getString("email"));

		// 完成后关闭
		rs.close();
		DBuitls.connclose();
		return users;
	}

	public List<Users> selectAll() throws SQLException, ClassNotFoundException {

		Statement stmt = DBuitls.getconn();

		String sql = "SELECT * FROM users";
		ResultSet rs = stmt.executeQuery(sql);

		List<Users> userlist = new ArrayList<Users>();

		// 展开结果集数据库
		while (rs.next()) {
			// 通过字段检索
			int id = rs.getInt("id");
			String name = rs.getString("name");
			String password = rs.getString("password");

			String email = rs.getString("email");

			userlist.add(new Users(id, name, password, email));

			// 输出数据
			// System.out.println("====ID: " + id+"====title: " + name+"=====content: " +
			// password);

		}
		// 完成后关闭
		rs.close();
		DBuitls.connclose();
		return userlist;
	}

}
